namespace Mavrick.Oracle.Entity.Oracle.Entity
{
	using System.Data.Common;
	using System.Data.Common.CommandTrees;
	using System.Data.Metadata.Edm;
	using System.Text;
		
	/// <summary>
	/// Class generating SQL for a DML command tree.
	/// </summary>
	internal static class OracleDmlQueryGenerator
	{
		/// <summary>
		/// 
		/// </summary>
		private const int CommandTextBuilderInitialCapacity = 256;
		
		/// <summary>
		/// 
		/// </summary>
		private const string ParameterPrefix = "@p";
		
		/// <summary>
		/// 
		/// </summary>
		/// <param name="tree"></param>
		/// <param name="providerManifest"></param>
		/// <param name="version"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		internal static string GenerateUpdateSql(DbUpdateCommandTree tree, OracleProviderManifest providerManifest, OracleVersion version, out List<DbParameter> parameters)
		{
			StringBuilder builder = new StringBuilder(CommandTextBuilderInitialCapacity);
			ExpressionTranslator translator = new ExpressionTranslator(builder, tree, null, != tree.Returning, version);
			
			if (0 == tree.SetClauses.Count)
			{
				builder.AppendLine("DECLARE " + ParameterPrefix + " INT");
			}
			
			builder.AppendLine("UPDATE ");
			tree.Target.Expression.Accept(translator);
			builder.AppendLine();
			
			builder.Append("SET ");

			string separator = string.Empty;
			foreach(DbSetClause clause in tree.SetClauses)
			{			
				builder.Append(separator);
				clause.Property.Accept(translator);
				builder.Append(" = ");
				clause.Value.Accept(translator);
				
				separator = ", ";
			}
			
			if (0 == tree.SetClauses.Count)
			{
				builder.Append(ParameterPrefix + " = 0");				
			}
			builder.AppendLine();
			builder.Append("WHERE ");
			tree.Predicate.Accept(translator);
			builder.AppendLine();
			
			// generate returning sql
			GenerateReturningSql(builder, tree, translator, tree.Returning, providerManifest, version);
			
			parameters = translator.Parameters;
			return builder.ToString();
		}
		
		/// <summary>
		/// 
		/// </summary>
		/// <param name="tree"></param>
		/// <param name="providerManifest"></param>
		/// <param name="version"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		internal static string GenerateDeleteSql(DbDeleteCommandTree tree, OracleProviderManifest providerManifest, OracleVersion version, out List<DbParameter> parameters)
		{
			StringBuilder builder = new StringBuilder(CommandTextBuilderInitialCapacity);
			ExpressionTranslator translator = new ExpressionTranslator(builder, tree, false, version);
			
			builder.Append("DELETE ");
			tree.Target.Expression.Accept(translator);
			
			builder.Append("WHERE ");
			tree.Predicate.Accept(translator);
			
			parameters = translator.Parameters;
			return builder.ToString();
		}
		
		/// <summary>
		/// 
		/// </summary>
		/// <param name="tree"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters)
		{
			StringBuilder builder = new StringBuilder(CommandTextBuilderInitialCapacity);
			ExpressionTranslator translator = new ExpressionTranslator(builder, tree, null != tree.Returning);
			
			builder.Append("INSERT ");
			tree.Target.Expression.Accept(translator);
			
			builder.Append("(");
			
			string separator = string.Empty;
			foreach(DbSetClause clause in tree.SetClauses)
			{	
				builder.Append(separator);
				clause.Property.Accept(translator);
				
				separator = ", ";
			}
			builder.AppendLine(")");
			
			separator = string.Empty;
			builder.Append("VALUES (");
			foreach(DbSetClause clause in tree.SetClauses)
			{
				builder.Append(separator);
				clause.Value.Accept(translator);
				translator.RegisterMemberValue(clause.Property, clause.Value);
				separator = ", ";
			}
			
			builder.AppendLine(")");
			
			// generate returning sq
			GenerateReturningSql(builder, tree, translator, tree.Returning);
			
			parameters = translator.Parameters;
			return builder.ToString();
		}
		
		private static string GenerateMemberTSql(EdmMember member)
		{
			return member.Name;
		}
		
		/// <summary>
		/// 
		/// </summary>
		/// <param name="builder"></param>
		/// <param name="tree"></param>
		/// <param name="translator"></param>
		/// <param name="returning"></param>
		/// <param name="providerManifest"></param>
		/// <param name="version"></param>
		private static void GenerateReturningSql(StringBuilder builder, DbModificationCommandTree tree, ExpressionTranslator translator, DbExpression returning, OracleProviderManifest providerManifest, OracleVersion version)
		{
			if (null == returning)
			{
				return;
			}
			
			EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
			
			StringBuilder block = new StringBuilder(50);
			block.AppendLine("DECLARE ");
			foreach(EdmMember key in table.ElementType.KeyMembers)
			{
				block.Append(GenerateMemberTSql(key));
				block.Append(" ");
				block.Append(OracleQueryGenerator.GetOraclePrimitiveType(providerManifest, version, key.TypeUsage));
				block.AppendLine(";");
			}
			
			block.AppendLine("BEGIN");
			builder.Insert(0, block.ToString());
			
			DbParameter cursor = translator.CreateParameter(OracleType.Cursor, ParameterDirection.Output);
			
			string separator = string.Empty;
			builder.AppendLine("RETURNING");
	
			foreach(EdmMember key in table.ElementType.KeyMembers)
			{
				builder.Append(separator);
				builder.Append(GenerateMemberTSql(key));
				separator = ", ";
			}
			
			builder.AppendLine(" INTO");
			
			separator = string.Empty;
			foreach(EdmMember key in table.ElementType.KeyMembers)
			{
				builder.Append(separator);
				builder.Append(GenerateMemberTSql(key));
				separator = ", ";
			}
			
			builder.AppendLine(";");
			builder.Append("OPEN ");
			builder.Append(cursor.ParameterName);
			builder.AppendLine(" FOR SELECT");
			
			separator = string.Empty;
			foreach(EdmMember key in table.ElementType.KeyMembers)
			{
				builder.Append(separator);
				builder.Append(GenerateMemberTSql(key));
				builder.Append(" AS ");
				builder.Append(GenerateMemberTSql(key));
				separator = ", ";
			}
			
			builder.AppendLine(" FROM DUAL;");
			builder.Append("END;");
		}
	}
}
